﻿using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using DataAccess.Exceptions;
using Model;

namespace DataAccess
{
    public static class StudentDataAccess
    {
        public static string ConnString = Conexion.DataSource;

        public static Student NewStudent(Student student)
        {
            if (student != null)
            {
                SqlConnection co = null;
                try
                {
                    co = new SqlConnection(ConnString);
                    SqlCommand cmd = co.CreateCommand();
                    cmd.CommandText = "INSERT INTO Estudiante (ci,nombre,apePat,apeMat,sexo,fechaNac,lugarNac,expedidoEn,licenciaAero,profesion,domicilio,zona,telefono,email,nombrePadre,profesionPadre,nombreMadre,profesionMadre,observaciones)" +
                                      " VALUES (@ci,@nombre,@apePat,@apeMat,@sexo,@fechaNac,@lugarNac,@expedidoEn,@licenciaAero,@profesion,@domicilio,@zona,@telefono,@email,@nombrePadre,@profesionPadre,@nombreMadre,@profesionMadre,@observaciones)";
                    cmd.Parameters.AddWithValue("ci", student.id);
                    cmd.Parameters.AddWithValue("nombre", student.name);
                    cmd.Parameters.AddWithValue("apePat", student.lastname);
                    cmd.Parameters.AddWithValue("apeMat", student.mlastname);
                    cmd.Parameters.AddWithValue("sexo", student.sex);
                    cmd.Parameters.AddWithValue("fechaNac", student.birthday.Date);
                    cmd.Parameters.AddWithValue("lugarNac", student.birthplace);
                    cmd.Parameters.AddWithValue("expedidoEn", student.issuedIn);
                    cmd.Parameters.AddWithValue("licenciaAero", student.liceAero);
                    cmd.Parameters.AddWithValue("profesion", student.profession);
                    cmd.Parameters.AddWithValue("domicilio", student.address);
                    cmd.Parameters.AddWithValue("zona", student.zone);
                    cmd.Parameters.AddWithValue("telefono", student.telephone);
                    cmd.Parameters.AddWithValue("email", student.email);
                    cmd.Parameters.AddWithValue("nombrePadre", student.fName);
                    cmd.Parameters.AddWithValue("profesionPadre", student.fProfession);
                    cmd.Parameters.AddWithValue("nombreMadre", student.mName);
                    cmd.Parameters.AddWithValue("profesionMadre", student.mProfession);
                    cmd.Parameters.AddWithValue("observaciones", student.observations);
                    co.Open();
                    int result = cmd.ExecuteNonQuery();
                    if (result <= 0)
                    {
                        throw new DataAccessException("Error al insertar estudiante! Revise el campo CI");
                    }

                }
                catch (SqlException ex)
                {
                    throw new DataAccessException("Error al insertar estudiante! Revise el campo CI", ex);
                }
                finally
                {
                    if (co != null)
                    {
                        co.Close();
                    }
                    SqlConnection.ClearAllPools();
                }
            }
            return student;
        }

        public static Collection<Student> GetStudentsByName(string name)
        {
            SqlConnection co = null;
            Collection<Student> studentsList = null;
            try
            {
                studentsList = new Collection<Student>();
                co = new SqlConnection(ConnString);
                SqlCommand cmd = co.CreateCommand();
                cmd.CommandText = "SELECT * FROM [Estudiante] WHERE nombre LIKE @V_nombre";
                cmd.Parameters.AddWithValue("V_nombre", name + "%");
                co.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Student studentResult = new Student();
                    studentResult.id = Convert.ToInt32(reader["ci"].ToString());
                    studentResult.name = reader["nombre"].ToString();
                    studentResult.lastname = reader["apePat"].ToString();
                    studentResult.mlastname = reader["apeMat"].ToString();
                    studentResult.sex = reader["sexo"].ToString();
                    studentResult.birthday = Convert.ToDateTime(reader["fechaNac"].ToString());
                    studentResult.birthplace = reader["lugarNac"].ToString();
                    studentResult.issuedIn = reader["expedidoEn"].ToString();
                    studentResult.liceAero = reader["licenciaAero"].ToString();
                    studentResult.profession = reader["profesion"].ToString();
                    studentResult.address = reader["domicilio"].ToString();
                    studentResult.zone = reader["zona"].ToString();
                    studentResult.telephone = reader["telefono"].ToString();
                    studentResult.email = reader["email"].ToString();
                    studentResult.fName = reader["nombrePadre"].ToString();
                    studentResult.fProfession = reader["profesionPadre"].ToString();
                    studentResult.mName = reader["nombreMadre"].ToString();
                    studentResult.mProfession = reader["profesionMadre"].ToString();
                    studentResult.observations = reader["observaciones"].ToString();

                    studentsList.Add(studentResult);
                }
                return (studentsList);
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error getting students", ex);
            }
            finally
            {
                if (co != null)
                {
                    co.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }
        public static Collection<Student> GetStudentsByID(int id)
        {
            SqlConnection co = null;
            Collection<Student> studentsList = null;
            try
            {
                studentsList = new Collection<Student>();
                co = new SqlConnection(ConnString);
                SqlCommand cmd = co.CreateCommand();
                cmd.CommandText = "SELECT * FROM [Estudiante] WHERE ci LIKE @V_ci";
                cmd.Parameters.AddWithValue("V_ci", id + "%");
                co.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Student studentResult = new Student();
                    studentResult.id = Convert.ToInt32(reader["ci"].ToString());
                    studentResult.name = reader["nombre"].ToString();
                    studentResult.lastname = reader["apePat"].ToString();
                    studentResult.mlastname = reader["apeMat"].ToString();
                    studentResult.sex = reader["sexo"].ToString();
                    studentResult.birthday = Convert.ToDateTime(reader["fechaNac"].ToString());
                    studentResult.birthplace = reader["lugarNac"].ToString();
                    studentResult.issuedIn = reader["expedidoEn"].ToString();
                    studentResult.liceAero = reader["licenciaAero"].ToString();
                    studentResult.profession = reader["profesion"].ToString();
                    studentResult.address = reader["domicilio"].ToString();
                    studentResult.zone = reader["zona"].ToString();
                    studentResult.telephone = reader["telefono"].ToString();
                    studentResult.email = reader["email"].ToString();
                    studentResult.fName = reader["nombrePadre"].ToString();
                    studentResult.fProfession = reader["profesionPadre"].ToString();
                    studentResult.mName = reader["nombreMadre"].ToString();
                    studentResult.mProfession = reader["profesionMadre"].ToString();
                    studentResult.observations = reader["observaciones"].ToString();

                    studentsList.Add(studentResult);
                }
                return (studentsList);
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error getting students by id", ex);
            }
            finally
            {
                if (co != null)
                {
                    co.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }
        public static Collection<Student> GetAllStudents()
        {
            SqlConnection co = null;
            Collection<Student> studentsList = null;
            try
            {
                studentsList = new Collection<Student>();
                co = new SqlConnection(ConnString);
                SqlCommand cmd = co.CreateCommand();
                cmd.CommandText = "SELECT * FROM [Estudiante]";
                co.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Student studentResult = new Student();
                    studentResult.id = Convert.ToInt32(reader["ci"].ToString());
                    studentResult.name = reader["nombre"].ToString();
                    studentResult.lastname = reader["apePat"].ToString();
                    studentResult.mlastname = reader["apeMat"].ToString();
                    studentResult.sex = reader["sexo"].ToString();
                    studentResult.birthday = Convert.ToDateTime(reader["fechaNac"].ToString());
                    studentResult.birthplace = reader["lugarNac"].ToString();
                    studentResult.issuedIn = reader["expedidoEn"].ToString();
                    studentResult.liceAero = reader["licenciaAero"].ToString();
                    studentResult.profession = reader["profesion"].ToString();
                    studentResult.address = reader["domicilio"].ToString();
                    studentResult.zone = reader["zona"].ToString();
                    studentResult.telephone = reader["telefono"].ToString();
                    studentResult.email = reader["email"].ToString();
                    studentResult.fName = reader["nombrePadre"].ToString();
                    studentResult.fProfession = reader["profesionPadre"].ToString();
                    studentResult.mName = reader["nombreMadre"].ToString();
                    studentResult.mProfession = reader["profesionMadre"].ToString();
                    studentResult.observations = reader["observaciones"].ToString();

                    studentsList.Add(studentResult);
                }
                return (studentsList);
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error getting students", ex);
            }
            finally
            {
                if (co != null)
                {
                    co.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }
        public static Student ModifyStudent(Student student)
        {
            if (student != null)
            {

                SqlConnection connection = null;
                connection = new SqlConnection(ConnString);
                try
                {
                    SqlCommand command = connection.CreateCommand();
                    command.CommandText = "UPDATE Estudiante SET nombre=@nombre,apePat=@apePat,apeMat=@apeMat,sexo=@sexo,fechaNac=@fechaNac,lugarNac=@lugarNac,  expedidoEn=@expedidoEn, licenciaAero=@licenciaAero, profesion=@profesion, domicilio=@domicilio,zona=@zona,telefono=@telefono,email=@email,nombrePadre=@nombrePadre,profesionPadre=@profesionPadre,nombreMadre=@nombreMadre,profesionMadre=@profesionMadre,observaciones=@observaciones  WHERE ci=@ci";
                    command.Parameters.AddWithValue("nombre", student.name);
                    command.Parameters.AddWithValue("apePat", student.lastname);
                    command.Parameters.AddWithValue("apeMat", student.mlastname);
                    command.Parameters.AddWithValue("sexo", student.sex);
                    command.Parameters.AddWithValue("fechaNac", student.birthday.Date);
                    command.Parameters.AddWithValue("lugarNac", student.birthplace); 
                    command.Parameters.AddWithValue("expedidoEn", student.issuedIn);
                    command.Parameters.AddWithValue("licenciaAero", student.liceAero);
                    command.Parameters.AddWithValue("profesion", student.profession);
                    command.Parameters.AddWithValue("domicilio", student.address);
                    command.Parameters.AddWithValue("zona", student.zone);
                    command.Parameters.AddWithValue("telefono", student.telephone);
                    command.Parameters.AddWithValue("email", student.email);
                    command.Parameters.AddWithValue("nombrePadre", student.fName);
                    command.Parameters.AddWithValue("profesionPadre", student.fProfession);
                    command.Parameters.AddWithValue("nombreMadre", student.mName);
                    command.Parameters.AddWithValue("profesionMadre", student.mProfession);
                    command.Parameters.AddWithValue("observaciones", student.observations);
                    command.Parameters.AddWithValue("ci", student.id);
                    connection.Open();
                    int result = command.ExecuteNonQuery();
                    if (result <= 0)
                    {
                        throw new DataAccessException("Error modifying a student");
                    }
                }
                catch (SqlException ex)
                {
                    throw new DataAccessException("Error modifying a student", ex);
                }
                finally
                {
                    if (connection != null)
                    {
                        connection.Close();
                    }
                    SqlConnection.ClearAllPools();
                }
            }
            return student;
        }
        public static Student GetOneStudentByID(int id)
        {
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection(ConnString);
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "SELECT * FROM [Estudiante] Where ci = @ci";
                cmd.Parameters.AddWithValue("ci", id);
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                if (!reader.HasRows)
                {
                    return null;
                }
                Student studentResult = new Student();

                while (reader.Read())
                {
                    studentResult.id = Convert.ToInt32(reader["ci"].ToString());
                    studentResult.name = reader["nombre"].ToString();
                    studentResult.lastname = reader["apePat"].ToString();
                    studentResult.mlastname = reader["apeMat"].ToString();
                    studentResult.sex = reader["sexo"].ToString();
                    studentResult.birthday = Convert.ToDateTime(reader["fechaNac"].ToString());
                    studentResult.birthplace = reader["lugarNac"].ToString();
                    studentResult.issuedIn = reader["expedidoEn"].ToString();
                    studentResult.liceAero = reader["licenciaAero"].ToString();
                    studentResult.profession = reader["profesion"].ToString();
                    studentResult.address = reader["domicilio"].ToString();
                    studentResult.zone = reader["zona"].ToString();
                    studentResult.telephone = reader["telefono"].ToString();
                    studentResult.email = reader["email"].ToString();
                    studentResult.fName = reader["nombrePadre"].ToString();
                    studentResult.fProfession = reader["profesionPadre"].ToString();
                    studentResult.mName = reader["nombreMadre"].ToString();
                    studentResult.mProfession = reader["profesionMadre"].ToString();
                    studentResult.observations = reader["observaciones"].ToString();
                }
                return studentResult;
            }
            catch (SqlException ex)
            {
                throw new DataAccessException("Error getting an student", ex);
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }
                SqlConnection.ClearAllPools();
            }
        }

    }
}
